import os
import pymysql
#
# csv_file_path = os.path.expanduser('~/Downloads/order_all_time.tsv')
#
# os.makedirs(os.path.dirname(csv_file_path), exist_ok=True)
#
#
# connection = pymysql.connect(host='localhost', user='admin', passwd='ZI94PIQJesVjQhC9OQYR', db='information_schema', port=3319)
#
# cursor = connection.cursor()
#
# cursor.execute("SHOW DATABASES LIKE 'lk_%'")
# databases = [row[0] for row in cursor.fetchall()]
#
# for db in databases:
#     code = db[3:]
#
#     #query = f"SELECT '{code}', express_no FROM {db}.opr_shipment ost WHERE ost.status = 6 AND ost.flag & 512 != 512 AND NOT EXISTS (SELECT 1 FROM {db}.opr_product WHERE shipment_id = ost.id AND flag & 512 != 512 AND status <> 12)"
#     #query = f"SELECT '{code}', barcode FROM {db}.opr_product WHERE `status` != '13' and shipment_id in (SELECT id FROM {db}.opr_shipment WHERE `status` = '8' and flag & 512 != 512) and flag & 512 != 512"
#     query = f"SELECT '{code}', order_no FROM {db}.opr_shipment WHERE `status` = '8' and flag & 512 != 512 group by order_no"
#     #query = f"SELECT '{code}', count(*) FROM {db}.tkt_route WHERE `key` = 'ExFACTORY'"
#
#     #query = f"SELECT op.order_no,sc.`name`,sp.create_at FROM {db}.opr_pickup op LEFT JOIN {db}.sta_pickup sp on op.id = sp.pickup_id LEFT JOIN {db}.opr_shipment os on op.order_no = os.order_no LEFT JOIN {db}.sys_channel sc on op.channel_id = sc.id WHERE sp.`status` = '2' and sp.create_at < '2024-02-01 00:00:00' and os.`status` != '8'"
#     #query = f'SELECT DISTINCT op.order_no,DATE_ADD(sp1.create_at, INTERVAL 8 HOUR) AS 入场单在途时间,DATE_ADD(sp2.create_at, INTERVAL 8 HOUR) AS 入场单已签收时间,DATE_ADD(sp3.create_at, INTERVAL 8 HOUR) AS 入场单核查中时间,DATE_ADD(sp4.create_at, INTERVAL 8 HOUR) AS 入场单已入场时间,DATE_ADD(ss1.create_at, INTERVAL 8 HOUR) AS 出场单等待中时间,DATE_ADD(ss2.create_at, INTERVAL 8 HOUR) AS 出场单物流下单时间,DATE_ADD(ss3.create_at, INTERVAL 8 HOUR) AS 出场单待拣货时间,DATE_ADD(ss4.create_at, INTERVAL 8 HOUR) AS 出场单拣货中时间,DATE_ADD(ss5.create_at, INTERVAL 8 HOUR) AS 出场单已拣货时间,DATE_ADD(ss6.create_at, INTERVAL 8 HOUR) AS 出场单装箱中时间,DATE_ADD(ss7.create_at, INTERVAL 8 HOUR) AS 出场单已装箱时间,DATE_ADD(ss8.create_at, INTERVAL 8 HOUR) AS 出场单已出厂时间 FROM `opr_pickup` op LEFT JOIN `sta_pickup` sp1 ON op.id = sp1.pickup_id AND sp1.status = 1 LEFT JOIN `sta_pickup` sp2 ON op.id = sp2.pickup_id AND sp2.status = 2 LEFT JOIN `sta_pickup` sp3 ON op.id = sp3.pickup_id AND sp3.status = 3 LEFT JOIN `sta_pickup` sp4 ON op.id = sp4.pickup_id AND sp4.status = 4 LEFT JOIN `opr_shipment` os ON op.order_no = os.order_no LEFT JOIN `sta_shipment` ss1 on os.id = ss1.shipment_id AND ss1.status = 1 LEFT JOIN `sta_shipment` ss2 on os.id = ss2.shipment_id AND ss2.status = 2 LEFT JOIN `sta_shipment` ss3 on os.id = ss3.shipment_id AND ss3.status = 3 LEFT JOIN `sta_shipment` ss4 on os.id = ss4.shipment_id AND ss4.status = 4 LEFT JOIN `sta_shipment` ss5 on os.id = ss5.shipment_id AND ss5.status = 5 LEFT JOIN `sta_shipment` ss6 on os.id = ss6.shipment_id AND ss6.status = 6 LEFT JOIN `sta_shipment` ss7 on os.id = ss7.shipment_id AND ss7.status = 7 LEFT JOIN `sta_shipment` ss8 on os.id = ss8.shipment_id AND ss8.status = 8 LEFT JOIN `sys_channel` sc on op.channel_id = sc.id WHERE op.create_at > "2024-03-20" and sc.code = "dajingxi";'
#
#
#     cursor.execute(query)
#     results = cursor.fetchall()
#
#     with open(csv_file_path, 'a') as csv_file:
#         csv_file.write("order_no\t入场单在途时间\t入场单已签收时间\t入场单核查中时间\t入场单已入场时间\t出场单等待中时间\t出场单物流下单时间\t出场单待拣货时间\t出场单拣货中时间\t出场单已拣货时间\t出场单装箱中时间\t出场单已装箱时间\t出场单已出厂时间\n")
#         for result in results:
#             csv_file.write('\t'.join(map(str, result)) + '\n')
#             #csv_file.write(f"{result[1]}\n")
# cursor.close()
# connection.close()

# csv_file_path = os.path.expanduser('~/Downloads/order_all_time.tsv')
#
# os.makedirs(os.path.dirname(csv_file_path), exist_ok=True)
#
# connection = pymysql.connect(host='localhost', user='admin', passwd='ZI94PIQJesVjQhC9OQYR', db='information_schema', port=3319)
#
# cursor = connection.cursor()
#
# cursor.execute("SHOW DATABASES LIKE 'lk_%'")
# databases = [row[0] for row in cursor.fetchall()]
# print(databases)
#
# with open(csv_file_path, 'w') as csv_file:
#     csv_file.write("order_no\t入场单在途时间\t入场单已签收时间\t入场单核查中时间\t入场单已入场时间\t出场单等待中时间\t出场单物流下单时间\t出场单待拣货时间\t出场单拣货中时间\t出场单已拣货时间\t出场单装箱中时间\t出场单已装箱时间\t出场单已出厂时间\n")
#     for db in databases:
#         connection = pymysql.connect(host='localhost', user='admin', passwd='ZI94PIQJesVjQhC9OQYR',
#                                      db=db, port=3319)
#         cursor = connection.cursor()
#         query = f"""
#                 SELECT DISTINCT op.order_no,
#                        DATE_ADD(sp1.create_at, INTERVAL 8 HOUR) AS 入场单在途时间,
#                        DATE_ADD(sp2.create_at, INTERVAL 8 HOUR) AS 入场单已签收时间,
#                        DATE_ADD(sp3.create_at, INTERVAL 8 HOUR) AS 入场单核查中时间,
#                        DATE_ADD(sp4.create_at, INTERVAL 8 HOUR) AS 入场单已入场时间,
#                        DATE_ADD(ss1.create_at, INTERVAL 8 HOUR) AS 出场单等待中时间,
#                        DATE_ADD(ss2.create_at, INTERVAL 8 HOUR) AS 出场单物流下单时间,
#                        DATE_ADD(ss3.create_at, INTERVAL 8 HOUR) AS 出场单待拣货时间,
#                        DATE_ADD(ss4.create_at, INTERVAL 8 HOUR) AS 出场单拣货中时间,
#                        DATE_ADD(ss5.create_at, INTERVAL 8 HOUR) AS 出场单已拣货时间,
#                        DATE_ADD(ss6.create_at, INTERVAL 8 HOUR) AS 出场单装箱中时间,
#                        DATE_ADD(ss7.create_at, INTERVAL 8 HOUR) AS 出场单已装箱时间,
#                        DATE_ADD(ss8.create_at, INTERVAL 8 HOUR) AS 出场单已出厂时间
#                 FROM `opr_pickup` op
#                 LEFT JOIN `sta_pickup` sp1 ON op.id = sp1.pickup_id AND sp1.status = 1
#                 LEFT JOIN `sta_pickup` sp2 ON op.id = sp2.pickup_id AND sp2.status = 2
#                 LEFT JOIN `sta_pickup` sp3 ON op.id = sp3.pickup_id AND sp3.status = 3
#                 LEFT JOIN `sta_pickup` sp4 ON op.id = sp4.pickup_id AND sp4.status = 4
#                 LEFT JOIN `opr_shipment` os ON op.order_no = os.order_no
#                 LEFT JOIN `sta_shipment` ss1 ON os.id = ss1.shipment_id AND ss1.status = 1
#                 LEFT JOIN `sta_shipment` ss2 ON os.id = ss2.shipment_id AND ss2.status = 2
#                 LEFT JOIN `sta_shipment` ss3 ON os.id = ss3.shipment_id AND ss3.status = 3
#                 LEFT JOIN `sta_shipment` ss4 ON os.id = ss4.shipment_id AND ss4.status = 4
#                 LEFT JOIN `sta_shipment` ss5 ON os.id = ss5.shipment_id AND ss5.status = 5
#                 LEFT JOIN `sta_shipment` ss6 ON os.id = ss6.shipment_id AND ss6.status = 6
#                 LEFT JOIN `sta_shipment` ss7 ON os.id = ss7.shipment_id AND ss7.status = 7
#                 LEFT JOIN `sta_shipment` ss8 ON os.id = ss8.shipment_id AND ss8.status = 8
#                 LEFT JOIN `sys_channel` sc ON op.channel_id = sc.id
#                 WHERE op.create_at > "2024-03-20" AND sc.code = "dajingxi";
#             """
#         cursor.execute(query)
#         results = cursor.fetchall()
#         for result in results:
#             csv_file.write('\t'.join(map(str, result)) + '\n')
#
# cursor.close()
# connection.close()
import os
import pandas as pd
import pymysql

csv_file_path = os.path.expanduser('~/Downloads/product_all_time.tsv')

# 创建目录
os.makedirs(os.path.dirname(csv_file_path), exist_ok=True)

# 建立连接
connection = pymysql.connect(host='localhost', user='admin', passwd='ZI94PIQJesVjQhC9OQYR',
                             db='information_schema', port=3319)

cursor = connection.cursor()

# 获取数据库列表
cursor.execute("SHOW DATABASES LIKE 'lk_%'")
databases = [row[0] for row in cursor.fetchall()]

# 创建 CSV 文件并写入表头
with open(csv_file_path, 'w') as csv_file:
    # csv_file.write("order_no\t入场单在途时间\t入场单已签收时间\t入场单核查中时间\t入场单已入场时间\t出场单等待中时间\t"
    #                "出场单物流下单时间\t出场单待拣货时间\t出场单拣货中时间\t出场单已拣货时间\t出场单装箱中时间\t出场单已装箱时间\t"
    #                "出场单已出厂时间\n")
    csv_file.write("order_no\tbarcode\t衣物核查中时间\t衣物待洗涤时间\t衣物洗涤中时间\t衣物已洗涤时间\t衣物待质检时间\t"
                   "衣物配附件时间\t衣物待包装时间\t衣物已包装时间\t衣物已上架时间\t衣物待拣货时间\t衣物已拣货时间\t"
                   "衣物已装箱时间\t衣物已出厂时间\n")

    # 遍历数据库
    for db in databases:
        connection.select_db(db)  # 选择数据库
        # cursor.execute("""
        #                 SELECT DISTINCT op.order_no,
        #                        DATE_ADD(sp1.create_at, INTERVAL 8 HOUR) AS 入场单在途时间,
        #                        DATE_ADD(sp2.create_at, INTERVAL 8 HOUR) AS 入场单已签收时间,
        #                        DATE_ADD(sp3.create_at, INTERVAL 8 HOUR) AS 入场单核查中时间,
        #                        DATE_ADD(sp4.create_at, INTERVAL 8 HOUR) AS 入场单已入场时间,
        #                        DATE_ADD(ss1.create_at, INTERVAL 8 HOUR) AS 出场单等待中时间,
        #                        DATE_ADD(ss2.create_at, INTERVAL 8 HOUR) AS 出场单物流下单时间,
        #                        DATE_ADD(ss3.create_at, INTERVAL 8 HOUR) AS 出场单待拣货时间,
        #                        DATE_ADD(ss4.create_at, INTERVAL 8 HOUR) AS 出场单拣货中时间,
        #                        DATE_ADD(ss5.create_at, INTERVAL 8 HOUR) AS 出场单已拣货时间,
        #                        DATE_ADD(ss6.create_at, INTERVAL 8 HOUR) AS 出场单装箱中时间,
        #                        DATE_ADD(ss7.create_at, INTERVAL 8 HOUR) AS 出场单已装箱时间,
        #                        DATE_ADD(ss8.create_at, INTERVAL 8 HOUR) AS 出场单已出厂时间
        #                 FROM opr_pickup op
        #                 LEFT JOIN sta_pickup sp1 ON op.id = sp1.pickup_id AND sp1.status = 1
        #                 LEFT JOIN sta_pickup sp2 ON op.id = sp2.pickup_id AND sp2.status = 2
        #                 LEFT JOIN sta_pickup sp3 ON op.id = sp3.pickup_id AND sp3.status = 3
        #                 LEFT JOIN sta_pickup sp4 ON op.id = sp4.pickup_id AND sp4.status = 4
        #                 LEFT JOIN opr_shipment os ON op.order_no = os.order_no
        #                 LEFT JOIN sta_shipment ss1 ON os.id = ss1.shipment_id AND ss1.status = 1
        #                 LEFT JOIN sta_shipment ss2 ON os.id = ss2.shipment_id AND ss2.status = 2
        #                 LEFT JOIN sta_shipment ss3 ON os.id = ss3.shipment_id AND ss3.status = 3
        #                 LEFT JOIN sta_shipment ss4 ON os.id = ss4.shipment_id AND ss4.status = 4
        #                 LEFT JOIN sta_shipment ss5 ON os.id = ss5.shipment_id AND ss5.status = 5
        #                 LEFT JOIN sta_shipment ss6 ON os.id = ss6.shipment_id AND ss6.status = 6
        #                 LEFT JOIN sta_shipment ss7 ON os.id = ss7.shipment_id AND ss7.status = 7
        #                 LEFT JOIN sta_shipment ss8 ON os.id = ss8.shipment_id AND ss8.status = 8
        #                 LEFT JOIN sys_channel sc ON op.channel_id = sc.id
        #                 WHERE op.create_at > "2024-03-01" AND op.flag & 512 != 512 and op.`status` != "5" AND sc.code = "dajingxi"
        #                 """)
        cursor.execute("""
                        select pi.order_no,op.barcode ,
                               DATE_ADD(sp1.create_at, INTERVAL 8 HOUR) AS 衣物核查中时间,
                               DATE_ADD(sp2.create_at, INTERVAL 8 HOUR) AS 衣物待洗涤时间,
                               DATE_ADD(sp3.create_at, INTERVAL 8 HOUR) AS 衣物洗涤中时间,
                               DATE_ADD(sp4.create_at, INTERVAL 8 HOUR) AS 衣物已洗涤时间,
                               DATE_ADD(sp5.create_at, INTERVAL 8 HOUR) AS 衣物待质检时间,
                               DATE_ADD(sp6.create_at, INTERVAL 8 HOUR) AS 衣物配附件时间,
                               DATE_ADD(sp7.create_at, INTERVAL 8 HOUR) AS 衣物待包装时间,
                               DATE_ADD(sp8.create_at, INTERVAL 8 HOUR) AS 衣物已包装时间,
                               DATE_ADD(sp9.create_at, INTERVAL 8 HOUR) AS 衣物已上架时间,
                               DATE_ADD(sp10.create_at, INTERVAL 8 HOUR) AS 衣物待拣货时间,
                               DATE_ADD(sp11.create_at, INTERVAL 8 HOUR) AS 衣物已拣货时间,
                               DATE_ADD(sp12.create_at, INTERVAL 8 HOUR) AS 衣物已装箱时间,
                               DATE_ADD(sp13.create_at, INTERVAL 8 HOUR) AS 衣物已出厂时间
                        from opr_product op  	
                        LEFT JOIN sta_product sp1 on op.id = sp1.product_id AND sp1.`status` = 1
                        LEFT JOIN sta_product sp2 on op.id = sp2.product_id AND sp2.`status` = 2											
                        LEFT JOIN sta_product sp3 on op.id = sp3.product_id AND sp3.`status` = 3
                        LEFT JOIN sta_product sp4 on op.id = sp4.product_id AND sp4.`status` = 4											
                        LEFT JOIN sta_product sp5 on op.id = sp5.product_id AND sp5.`status` = 5											
                        LEFT JOIN sta_product sp6 on op.id = sp6.product_id AND sp6.`status` = 6											
                        LEFT JOIN sta_product sp7 on op.id = sp7.product_id AND sp7.`status` = 7											
                        LEFT JOIN sta_product sp8 on op.id = sp8.product_id AND sp8.`status` = 8											
                        LEFT JOIN sta_product sp9 on op.id = sp9.product_id AND sp9.`status` = 9											
                        LEFT JOIN sta_product sp10 on op.id = sp10.product_id AND sp10.`status` = 10											
                        LEFT JOIN sta_product sp11 on op.id = sp11.product_id AND sp11.`status` = 11											
                        LEFT JOIN sta_product sp12 on op.id = sp12.product_id AND sp12.`status` = 12											
                        LEFT JOIN sta_product sp13 on op.id = sp13.product_id AND sp13.`status` = 13	
                        LEFT JOIN sys_channel sc ON op.channel_id = sc.id
                        LEFT JOIN opr_pickup pi ON op.pickup_id = pi.id
                        WHERE pi.create_at > "2024-03-01" AND op.companion_id is null and op.flag & 512 != 512 AND sc.code = "dajingxi"
                        """)
        # 将结果写入 CSV 文件
        for row in cursor.fetchall():
            csv_file.write('\t'.join(map(str, row)) + '\n')

cursor.close()
connection.close()
